﻿using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Utility;

namespace Utility
{
    public class Database
    {

        public static string DefauleDbConnection = "";
        public Database()
        {
            var Configuration = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: true)
                .Build();
            DefauleDbConnection = Configuration.GetSection("AppSettings:ConnectionString").Value;
            dbConnection = new SqlConnection(DefauleDbConnection); ;
        }
        public IDbConnection dbConnection { get; set; }
        public IDbTransaction dbTransaction { get; set; }

        #region  事务操作部分
        public Database BeginTrans()
        {
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            dbTransaction = dbConnection.BeginTransaction();
            return this;
        }
        /// <summary>
        /// 提交当前操作的结果
        /// </summary>
        public void Commit()
        {
            try
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Commit();
                    this.Close();
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        /// <summary>
        /// 把当前操作回滚成未提交状态
        /// </summary>
        public void Rollback()
        {
            this.dbTransaction.Rollback();
            this.dbTransaction.Dispose();
            this.Close();
        }
        /// <summary>
        /// 关闭连接 内存回收
        /// </summary>
        public void Close()
        {
            dbConnection.Close();
            //dbConnection.Dispose();
        }
        #endregion
        public int Execute(string strSql)
        {
            try
            {
                return dbConnection.Execute(strSql, null, dbTransaction);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public int Execute(string strSql, object dbParameter)
        {
            try
            {
                return dbConnection.Execute(strSql, dbParameter, dbTransaction);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public int ExecuteScalar(string strSql)
        {
            try
            {
                return dbConnection.ExecuteScalar<int>(strSql, null, dbTransaction);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public int ExecuteScalar(string strSql, object dbParameter)
        {
            try
            {
                return dbConnection.ExecuteScalar<int>(strSql, dbParameter, dbTransaction);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public int Insert<T>(T entity) where T : class
        {
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = entity.GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();

            foreach (var p in ps)
            {
                if (p.Name != pkName && p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2}) select @@identity", tableName, string.Join(", ", @colms), string.Join(", ", @params));
            return ExecuteScalar(sql, entity);
        }
        public int Update<T>(T entity) where T : class
        {
            string where = "";
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var listPropert = entity.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                listPropert.ToList().ForEach(p =>
                {

                    if (pkName == p.Name)
                    {
                        where += $" {p.Name} = @{p.Name} and";
                    }
                });
            }

            where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("未找到Id");
            }

            string update = "";
            var listPropertUpdate = entity.GetType().GetProperties();
            if (listPropertUpdate.Length > 0)
            {
                update += "";
                listPropertUpdate.ToList().ForEach(e =>
                {
                    if (e.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute)) && e.Name != pkName)
                    {
                        update += $"{e.Name} = @{e.Name} ,";
                    }
                });
            }
            update = update.TrimEnd(',');
            if (string.IsNullOrEmpty(update))
            {
                throw new Exception("无更新内容");
            }
            string query = $"update { tableName} set {update} where {where}";
            return Execute(query, entity);
        }
        public DataTable GetTableBySql(string strSql)
        {
            try
            {
                var reader = dbConnection.ExecuteReader(strSql, null, dbTransaction);
                DataTable table = new DataTable();
                table.Load(reader);
                return table;

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public List<T> GetListBySql<T>(string strSql) where T : class
        {
            try
            {
                return dbConnection.Query<T>(strSql, null, dbTransaction).ToList();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }
        public int Insert2<T>(T entity) where T : class
        {
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            pkName = "";
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = entity.GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();

            foreach (var p in ps)
            {
                if (p.Name != pkName && p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = string.Format("SET IDENTITY_INSERT {0} ON INSERT INTO [{0}] ({1}) VALUES({2}) SET IDENTITY_INSERT {0} OFF select @@identity", tableName, string.Join(", ", @colms), string.Join(", ", @params));
            return ExecuteScalar(sql, entity);
        }
        public void Dispose()
        {
            dbConnection.Close();
        }
        public List<T> GetList<T>()
        {
            string tableName = CommonHelper.GetTableField<T>().ToString();
            return dbConnection.Query<T>("select * from " + tableName + " where 1=1 and Status=0 ", null, dbTransaction).ToList();
        }
        public List<T> GetListByIds<T>(List<int> Ids)
        {
            string tableName = CommonHelper.GetTableField<T>().ToString();
            return dbConnection.Query<T>("select * from " + tableName + " where 1=1 and Status=0 and Id in @Ids ", new { Ids = Ids }, dbTransaction).ToList();
        }
        public T GetEntityById<T>(int Id)
        {
            string tableName = CommonHelper.GetTableField<T>().ToString();
            return DatabaseContext.QueryFirstOrDefault<T>("select * from " + tableName + " where Status != @Status and Id=@Id", new { Status = Status.delete, Id = Id });
        }
        public void DeleteByIds<T>(List<int> Ids)
        {
            string tableName = CommonHelper.GetTableField<T>().ToString();
            dbConnection.Execute(" Update " + tableName + " set Status=1 where Id in @Ids ", new { Ids = Ids });
        }
        public T GetEntityBySql<T>(string sql)
        {

            return DatabaseContext.QueryFirstOrDefault<T>(sql);
        }
    }

}
